using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace DBUtility
{
    public class DBHelper
    {
        private static string connectionString = "server=localhost;database=mess;userid=root;password=1488679583;";

        // 准备命令
        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, string cmdText, MySqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = CommandType.Text;

            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        // 执行查询并返回DataTable
        public static DataTable ExecuteDataTable(string cmdText, MySqlParameter[] cmdParms = null)
        {
            DataTable dt = new DataTable();
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand();
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
                {
                    adapter.Fill(dt);
                }
            }
            return dt;
        }

        // 执行非查询命令并返回影响的行数
        public static int ExecuteNonQuery(string cmdText, MySqlParameter[] cmdParms = null)
        {
            int rowsAffected = 0;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand();
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                rowsAffected = cmd.ExecuteNonQuery();
            }
            return rowsAffected;
        }

        // 执行查询并返回单个值
        public static object ExecuteScalar(string cmdText, MySqlParameter[] cmdParms = null)
        {
            object result = null;
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand();
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                result = cmd.ExecuteScalar();
            }
            return result;
        }

        // 执行带参数的查询并返回泛型列表
        public static List<T> ExecuteQuery<T>(string cmdText, MySqlParameter[] cmdParms, Func<MySqlDataReader, T> map)
        {
            List<T> list = new List<T>();
            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand();
                PrepareCommand(cmd, conn, cmdText, cmdParms);
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        list.Add(map(reader));
                    }
                }
            }
            return list;
        }
    }
}

